package com.bizmerce.rnd.oms.decision.data;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelData {

	public static List<BillOfMaterials> getCostedBOMfromExcel() {
		List<BillOfMaterials> bomResult = new ArrayList<BillOfMaterials>();
		
		try {
			// 엑셀파일 로드
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
					"C:/소스 및 컴파일/원산지/원스탑관세사/원스탐관세사자료/원산지확인서-GM(원주사업부)1.xls"));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			int sheetNum = wb.getNumberOfSheets();// 시트갯수 가져오기
			HSSFSheet sheet = wb.getSheetAt(2);// 시트 가져오기
			int rows = sheet.getPhysicalNumberOfRows();// 행 갯수 가져오기
			String oldPartNo = "";

			BillOfMaterials bom = null;
			for (int j = 2; j < rows; j++) {
				HSSFRow row = sheet.getRow(j);
				if (row != null) {
					int cells = row.getPhysicalNumberOfCells();// cell 갯수 가져오기
					HSSFCell partNoCell = row.getCell(15);
					String partNo = partNoCell.getStringCellValue();

					if (!oldPartNo.equals(partNo)) {
						bom = new BillOfMaterials();
						bomResult.add(bom);
						oldPartNo = partNo;
					}
					RawMaterial material = new RawMaterial();
					for (int k = 0; k < cells; k++) {
						String value = null;
						HSSFCell cell = row.getCell(k);
						if (cell != null) {
							switch (cell.getCellType()) {
							case HSSFCell.CELL_TYPE_FORMULA:
								value = "" + cell.getCellFormula();
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								value = "" + (double) cell.getNumericCellValue();
								break;
							case HSSFCell.CELL_TYPE_STRING:
								value = "" + cell.getStringCellValue();
								break;
							case HSSFCell.CELL_TYPE_BLANK:
								value = null;
								break;
							case HSSFCell.CELL_TYPE_BOOLEAN:
								value = "" + cell.getBooleanCellValue();
								break;
							case HSSFCell.CELL_TYPE_ERROR:
								value = "" + cell.getErrorCellValue();
								break;
							default:
							}

							switch (k) {
							case 0:
								material.setLevel((int)Double.parseDouble(value));
								break;
							// case 12:
							// bom.set(Integer.parseInt(value));
							// break;
							case 14:
								bom.setPartNoOfAlternateSource(value);
								break;
							case 15:
								bom.setPartNo(value);
								break;
							case 16:
								bom.setSalePrice(Double.parseDouble(value));
								break;
							case 17:
								material.setPartNo(value);
								break;
							case 18:
								material.setPurchasePrice(Double
										.parseDouble(value));
								break;
							// case 19:
							// bom.setIssueNo(value);
							// break;
							case 20:
								material.setCost(Double.parseDouble(value));
								break;
							default:
							}
						}
					}
					bom.addRawMaterials(material);
				}
			}
			System.out.println("제품 개수: " + bomResult.size());
			System.out.println("제품별 원재료 개수:");
			for (BillOfMaterials billOfMaterials : bomResult) {
				System.out.println("제품품명: " + billOfMaterials.getPartNo());
				System.out.println("원재료개수: "
						+ billOfMaterials.getRawMaterials().size());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return bomResult;
	}
	
	public static void main(String[] args) throws Exception {
		List<BillOfMaterials> boms = getCostedBOMfromExcel();
		
		Class.forName("oracle.jdbc.OracleDriver");
		String sql = "INSERT INTO OMS01.DEOU (DECIDISTNUM, AGREE_CD, GOOD_PART_NO, DELIV_PART_NO, GOODPRICE, HSCD, " +
				"ORIG_RULE, SATIYN, GOOD_NM, DES, DECIDATE, STAT) " +
				"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'A')";
		String sql2= "INSERT INTO OMS01.COSTEDBOM_PART (DECIDISTNUM, PART_NO, AGREE_CD, IM_MAT_YN, HIGH_PART_NO, PARTPRICE, ORIG, SN) " +
		"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)";

		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@112.166.132.225:1531:ORCL10", "oms01", "oms911");
		PreparedStatement pstmt = conn.prepareStatement(sql);
		PreparedStatement pstmt2 = conn.prepareStatement(sql2);
		//YYYYMMDDHHMISSSSS+RAND(3)
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmssSSS");
		
		for (BillOfMaterials bom : boms) {
			for(int i=0; i<2; i++) {
				String rand = String.valueOf((int)(Math.random() * 1000) + 1);
				if(rand.length() == 1) {
					rand = "00" + rand;
				} else if(rand.length() == 2) {
					rand = "0" + rand;
				}
				rand = format.format(new Date()) + rand;
				
				pstmt.setString(1, rand);
				if(i == 0)
					pstmt.setString(2, "EU");
				else 
					pstmt.setString(2, "US");
				pstmt.setString(3, bom.getPartNo());
				pstmt.setString(4, bom.getPartNoOfAlternateSource());
				pstmt.setDouble(5, bom.getSalePrice());
				pstmt.setString(6, "870894");
				pstmt.setString(7, "");
				pstmt.setString(8, "");
				pstmt.setString(9, "");
				pstmt.setString(10, new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()));
				pstmt.setString(11, bom.getPartNoOfAlternateSource());
				
				pstmt.addBatch();
				
				List<RawMaterial> raws = bom.getRawMaterials();
				int m = 0; 
				for (RawMaterial raw : raws) {
					pstmt2.setString(1, rand);
					pstmt2.setString(2, raw.getPartNo());
					if(i==0)
						pstmt2.setString(3, "EU");
					else 
						pstmt2.setString(3, "US");
					pstmt2.setString(4, "N");
					pstmt2.setString(5, bom.getPartNo());
					pstmt2.setDouble(6, raw.getPurchasePrice());
					pstmt2.setString(7, "");
					pstmt2.setInt(8, m++);
					
					pstmt2.addBatch();
				}
			}
		}
		pstmt.executeBatch();
		pstmt2.executeBatch();
		pstmt.close();
		pstmt2.close();
		conn.close();
	}
}